﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class ParentDAL : IParentDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public ParentDAL()
        {
        }

        public IList<ParentInfo> ListParents(ParentInfo pInfo)
        {
            string strSQL;
            int empSeq = pInfo.Employee == null ? 0 : pInfo.Employee.Sequence;

            List<ParentInfo> infoList = new List<ParentInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Seq", pInfo.Sequence);
                dbManager.AddParameters(2, "@EmpSeq", empSeq);

                strSQL = "SELECT p.PARENT_SEQ, p.EMP_SEQ, p.NAME_PREFIX_SEQ, p.PARENT_NAME"
                        + ", p.PARENT_LAST_NAME, p.BIRTH_DATE, p.PARENT_TYPE"
                        + ", CASE p.PARENT_TYPE "
                            + " WHEN '1' THEN 'บิดา'"
                            + " WHEN '2' THEN 'มารดา'"
                            + " WHEN '3' THEN 'บิดาบุญธรรม'"
                            + " WHEN '4' THEN 'มารดาบุญธรรม'"
                            + " WHEN '5' THEN 'บิดาคู่สมรส'"
                            + " WHEN '6' THEN 'มารดาคู่สมรส'"
                            + " ELSE 'อื่นๆ'"
                            + " END AS PARENT_TYPE_NAME"
                        + ", p.CARE_STATUS, p.PARENT_STATUS"
                        + ", CASE p.PARENT_STATUS"
                            + " WHEN '1' THEN 'มีชีวิตอยู่'"
                            + " WHEN '0' THEN 'เสียชีวิต'"
                            + " ELSE ''"
                            + " END AS PARENT_STATUS_NAME"
                        + ", p.CITIZEN, p.RECORD_STATUS, p.CREATED_BY, p.UPDATED_BY"
                        + ", CONCAT(np.PREFIX_NAME, p.PARENT_NAME, '  ', p.PARENT_LAST_NAME) AS FULL_NAME"
                    + " FROM HRM_PS_EMP_PARENTS p"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON p.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " WHERE p.RECORD_STATUS not like @DeleteRecord"
                    + (pInfo.Sequence > 0 ? " AND p.PARENT_SEQ = @Seq" : "")
                    + (empSeq > 0 ? " AND p.EMP_SEQ = @EmpSeq" : "")
                    + " ORDER BY p.PARENT_TYPE";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    EmployeeDAL empDAL = new EmployeeDAL();
                    EmployeeInfo empInfo = new EmployeeInfo();
                    if (!rdr.IsDBNull(1))
                    {
                        IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(1)));
                        empInfo = empList[0];
                    }

                    MasNamePrefixDAL pfDAL = new MasNamePrefixDAL();
                    MasNamePrefixInfo pfInfo = new MasNamePrefixInfo();
                    if (!rdr.IsDBNull(2))
                    {
                        IList<MasNamePrefixInfo> pfList = pfDAL.ListNamePrefixes(new MasNamePrefixInfo(rdr.GetInt32(2)));
                        pfInfo = pfList[0];
                    }

                    PersonInfo perInfo = new PersonInfo();
                    perInfo.FirstName = rdr.IsDBNull(3) ? "" : rdr.GetString(3);
                    perInfo.LastName = rdr.IsDBNull(4) ? "" : rdr.GetString(4);
                    perInfo.BirthDate = rdr.IsDBNull(5) ? (DateTime?)null : rdr.GetDateTime(5);
                    perInfo.Citizen = rdr.IsDBNull(11) ? "" : rdr.GetString(11);
                    perInfo.FullName = rdr.IsDBNull(15) ? "" : rdr.GetString(15);
                    perInfo.NamePrefix = pfInfo;

                    ParentInfo Info = new ParentInfo(rdr.GetInt32(0)
                            , empInfo
                            , perInfo
                            , rdr.IsDBNull(6) ? "" : rdr.GetString(6)
                            , rdr.IsDBNull(7) ? "" : rdr.GetString(7)
                            , rdr.GetString(8) == "1"
                            , rdr.IsDBNull(9) ? "" : rdr.GetString(9)
                            , rdr.IsDBNull(10) ? "" : rdr.GetString(10)
                            , rdr.IsDBNull(12) ? "" : rdr.GetString(12)
                            , rdr.IsDBNull(13) ? null : (Nullable<int>)rdr.GetInt32(13)
                            , rdr.IsDBNull(14) ? null : (Nullable<int>)rdr.GetInt32(14));
                    infoList.Add(Info);
                }

                return infoList;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Insert(ParentInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee == null ? (int?)null : Info.Employee.Sequence);
                dbManager.AddParameters(1, "@PrefixSeq", Info.Person.NamePrefix == null ? (int?)null : Info.Person.NamePrefix.Sequence);
                dbManager.AddParameters(2, "@Name", Info.Person.FirstName);
                dbManager.AddParameters(3, "@LastName", Info.Person.LastName);
                dbManager.AddParameters(4, "@BirthDate", Info.Person.BirthDate);
                dbManager.AddParameters(5, "@Type", Info.Type);
                dbManager.AddParameters(6, "@Status", Info.Status);
                dbManager.AddParameters(7, "@CareStatus", Info.CareStatus ? "1" : "0");
                dbManager.AddParameters(8, "@Citizen", Info.Person.Citizen);
                dbManager.AddParameters(9, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(10, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(11, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_EMP_PARENTS"
                    + "(EMP_SEQ, NAME_PREFIX_SEQ, PARENT_NAME, PARENT_LAST_NAME, BIRTH_DATE"
                    + ", PARENT_TYPE, PARENT_STATUS, CARE_STATUS, CITIZEN"
                    + ", RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES (@EmpSeq, @PrefixSeq, @Name, @LastName, @BirthDate, @Type"
                        + ", @Status, @CareStatus, @Citizen, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(ParentInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee == null ? (int?)null : Info.Employee.Sequence);
                dbManager.AddParameters(1, "@PrefixSeq", Info.Person.NamePrefix == null ? (int?)null : Info.Person.NamePrefix.Sequence);
                dbManager.AddParameters(2, "@Name", Info.Person.FirstName);
                dbManager.AddParameters(3, "@LastName", Info.Person.LastName);
                dbManager.AddParameters(4, "@BirthDate", Info.Person.BirthDate);
                dbManager.AddParameters(5, "@Type", Info.Type);
                dbManager.AddParameters(6, "@Status", Info.Status);
                dbManager.AddParameters(7, "@CareStatus", Info.CareStatus ? "1" : "0");
                dbManager.AddParameters(8, "@Citizen", Info.Person.Citizen);
                dbManager.AddParameters(9, "@Sequence", Info.Sequence);
                dbManager.AddParameters(10, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(11, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_EMP_PARENTS"
                        + " SET NAME_PREFIX_SEQ = @PrefixSeq"
                        + ", PARENT_NAME = @Name"
                        + ", PARENT_LAST_NAME = @LastName"
                        + ", BIRTH_DATE = @BirthDate"
                        + ", PARENT_TYPE = @Type"
                        + ", CARE_STATUS = @CareStatus"
                        + ", CITIZEN = @Citizen"
                        + ", PARENT_STATUS = @Status"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE PARENT_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);


                strSQL = "UPDATE HRM_PS_EMP_PARENTS"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE PARENT_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
